/*
 
 Program eneu_metro_counts.do creates the list of municipalities in 
 each metro area.

 Kumler, Verhoogen, Frias "Enlisting Employees ..." REStat forthcoming

*/


/*
Notes:

*/

****************************** housekeeping ********************************;
#delimit;
set more off;

do ${code}housekeeping.do;
set mem 5g;
set processors 8;

*********************** creation of eneu metro list ********************;


guse year qtr eneu entmpio_inegi metro_area using ${work}eneu19872004_web, clear;
 
collapse (sum) eneu, by(year qtr metro_area entmpio_inegi);

sort metro_area entmpio_inegi year qtr;

*create indicators;
by metro_area entmpio_inegi: gen first_obs=1 if _n==1;
gsort metro_area entmpio_inegi -year -qtr;
by metro_area entmpio_inegi: gen last_obs=1 if _n==1;
sort metro_area entmpio_inegi year qtr;
by metro_area entmpio_inegi: gen past_missing=1 if (year==year[_n-1] & qtr!=qtr[_n-1]+1) |
 (year==year[_n-1]+1 & (qtr!=1 | qtr[_n-1]!=4)) | (year-year[_n-1]>1 & year[_n-1]!=.);
by metro_area entmpio_inegi: gen inc50=1 if eneu>=1.5*eneu[_n-1] & eneu[_n-1]!=.;
by metro_area entmpio_inegi: gen dec50=1 if eneu<=0.5*eneu[_n-1] & eneu[_n-1]!=.;
gen under10=1 if eneu<=1;
by metro_area entmpio_inegi: egen missing_n=total(past_missing);
by metro_area entmpio_inegi: egen inc50_n=total(inc50);
by metro_area entmpio_inegi: egen dec50_n=total(dec50);
gen only_one=1 if first==1 & last==1;

gsave ${work}eneu_metro_counts, replace;


** Note: Some municipios appear within a metro area for only
   one quarter. Drop these;
drop if only_one==1;

**create a file with one observation per municipio that maps into a metro
  area with first year in data and last year in data;
gen first_year = year if first_obs==1;
gen first_qtr = qtr if first_obs==1;
by metro_area entmpio_inegi: egen last_year=sum(year*(last_obs==1));
by metro_area entmpio_inegi: egen last_qtr=sum(qtr*(last_obs==1));
by metro_area entmpio_inegi: keep if _n==1;

*check for municipios that are mapped to more than one metro area;
duplicates tag entmpio_inegi, gen(dup);

drop if entmpio_inegi=="11031" & metro_area==4;
drop if inlist(entmpio_inegi,"29025","29027","29041") & metro_area==39;

duplicates report entmpio_inegi;

keep metro_area entmpio_inegi first_year first_qtr last_year last_qtr;

sort entmpio_inegi metro_area;

save ${work}eneu_metro_list, replace;

log close;

